/*
 * koneksiDB.java
 *
 * Created on 11 Desember 2007, 18:41
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package sms_rac_v3;
import java.io.InputStream;
import java.net.URL;
import java.sql.*;
import java.util.*;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.JTextPane;
import net.sf.jasperreports.view.JasperViewer;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import java.io.OutputStream;
/**
 *
 * @author Rachmad HW
 */
public  class koneksiDB {
    
    /** Creates a new instance of koneksiDB */
   /* static String dbDrv = "org.postgresql.Driver";
    static String dbUrl = "jdbc:postgresql://localhost:5432/rpulsa";
    static String user  = "postgres";
    static String passwd  = "rpulsa";
    */
    static String dbDrv = "com.mysql.jdbc.Driver";
    static String dbUrl = "jdbc:mysql://localhost/sms";
    static String user  = "root";
    static String passwd  = "admin";
    
    //String passwd = Enkripsi.Dekripsi("bz4l0TuVLTMji2Nw4L608QZAfI");
    public static Connection con;
    public static Statement stat;
    public static ResultSet isi;
    String[] DayName = {"Senin","Selasa","Rabu","Kamis","Jumat","Sabtu","Minggu"};
    
    
    public static  void koneksiDB() {
        sambungDB();
    }
    
    /** Creates a new instance of KoneksiDB */
    
    public static void sambungDB(){
         try {
            Class.forName(dbDrv);
            con = DriverManager.getConnection(dbUrl,user,passwd);
            System.out.println ("Koneksi Berhasil !");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,"Terjadi Kesalahan. Cek Koneksi ODBC dan Databese POSGRESQL Anda!\nHubungi Administrator Anda! Terima kasih","Informasi",JOptionPane.ERROR_MESSAGE);
            System.out.println ("Driver Tidak ada !");
            System.out.println("err "+ ex);
            //System.exit(0);
        }
    }
    public static void tutupKoneksi() {
    	try {
            con.close();
        } catch (Exception ex) {}
    }
    public static void cetak(){
           koneksiDB apa= new koneksiDB();
    }
     public   void cetakLaporan(String Path){
        try{
            //String FileName[] = {"ABSENSI.jasper","NILAI.jasper","GRADE.jasper","ABSENSI_ALL.jasper","NILAI_ALL.jasper","GRADE_ALL.jasper"};
            InputStream path = getClass().getResourceAsStream("/Laporan/"+Path.trim());
           // Map parameters = new HashMap();
            //if(pil < 3) parameters.put("KODE", Kode);
            //parameters.put("PERIODE", Periode);
            JasperPrint jasperPrint = JasperFillManager.fillReport(path, null , con);
            JasperViewer.viewReport(jasperPrint,false);
        } catch (Exception er) {
            System.out.println(er);
            JOptionPane.showMessageDialog(null,"Terjadi kesalahan system, Pencetakan gagal!","Pesan",JOptionPane.INFORMATION_MESSAGE);
        }
    }
     /*
     public void cetakLaporan(int pil, String Kode, String Periode){
        try{
            String FileName[] = {"ABSENSI.jasper","NILAI.jasper","GRADE.jasper","ABSENSI_ALL.jasper","NILAI_ALL.jasper","GRADE_ALL.jasper"};
            InputStream path = getClass().getResourceAsStream("/silabuntag/Laporan/"+FileName[pil].trim());
            Map parameters = new HashMap();
            if(pil < 3) parameters.put("KODE", Kode);
            parameters.put("PERIODE", Periode);
            JasperPrint jasperPrint = JasperFillManager.fillReport(path, parameters, con);
            JasperViewer.viewReport(jasperPrint,false);
        } catch (Exception er) {
            System.out.println(er);
            JOptionPane.showMessageDialog(null,"Terjadi kesalahan system, Pencetakan gagal!","Pesan",JOptionPane.INFORMATION_MESSAGE);
        }
    }
      */
     public static boolean cekUser(String pengguna){ // true jika ada / ketemu
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_pengguna WHERE pengguna = '" + pengguna.toLowerCase() + "'";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalah cek user " + e.getMessage());
            return false;
        }
    }
     public static boolean cekValidKode(String kode_pulsa){ // true jika ada / ketemu
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_pulsa WHERE kode_pulsa = '" + kode_pulsa.toUpperCase() + "'";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalahan cek valid kode " + e.getMessage());
            return false;
        }
    }
     public static boolean cekValidKode_konsumen(String kode_konsumen){ // true jika ada / ketemu
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_konsumen WHERE kode_konsumen = " + kode_konsumen.trim() + ";";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalahan cek valid kode konsumen " + e.getMessage());
            return false;
        }
    }
      public static boolean cekValidPin(String notlp,String pin){ // true jika ada / ketemu
        String kode = ambilKonsumen(notlp);
        System.out.println("no tlpnya: "+notlp);
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_konsumen WHERE sandi = " + pin.trim() + " and " +
                "kode_konsumen = "+kode+";";
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalah cek valid pin:" + e.getMessage());
            return false;
        }
    }
       public static boolean cekNoHPKonsumen(String notlp){ // true jika ada / ketemu
        String kode = ambilKonsumen(notlp);
        System.out.println("no tlpnya: "+notlp);
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_detail_konsumen WHERE no_hp = " + notlp+";"; 
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalah cek valid pin:" + e.getMessage());
            return false;
        }
    }
    public static int hitung_data(String perintah){
        int nilai=0;
        String query = "SELECT COUNT(*) AS JUMLAH FROM " + perintah.toLowerCase() + "";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            nilai = Integer.valueOf(hasil);
            //return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("Ada kesalahan di proses hitung_data ( " + e.getMessage()+" )");
            //return false;
        }
        return nilai;
    }
    public static boolean cekAda(String no7an, String kode_pulsa,String tgl){
        int nilai=0;
        String query = "SELECT COUNT(*) AS JUMLAH FROM tb_transaksi where no_tujuan=" + no7an+" and kode_pulsa='"+kode_pulsa+"' and tgl ='"+tgl+"' ;";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            nilai = Integer.valueOf(hasil);
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("Ada kesalahan di proses hitung_data ( " + e.getMessage()+" )");
            return false;
        }
        //return nilai;
    }
    public static String[] baca_alat(){
        String alat[] = new String[30];
        int a=0;
         String query = "select * from tb_alat";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                //untuk mengambil nama port yang terdaftar
                
                alat[a]=isi.getString("port_com");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Ada kesalahan pada proses baca_alat, Kesalahan: " + e.getMessage());
          
        }
        
        
        return alat;
    }
    public static boolean cekUserPassword(String pengguna, String sandi){ // true jika cocok 
        String query = "SELECT sandi FROM tb_pengguna WHERE LOWER(pengguna) = '" + pengguna.trim() + "'";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "";
            isi.next(); hasil = isi.getString("sandi");
            stat.close();
            //hasil = Enkripsi.Dekripsi(hasil);
            //return hasil.equals(sandi);
            return true;
        } catch(Exception e) {
            System.out.println ("kesalahan cek user passwd: " + e.getMessage());
            return false;
        }
    }
     public static void tambahUser(String pengguna, String sandi){ // true jika ada / ketemu
        String query = "insert into tb_pengguna values ('" + pengguna.trim() + "','"+sandi.trim()+"',0)";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah user: " + e.getMessage());
          
        }
    }
     public static void hapusUser(String pengguna){ // true jika ada / ketemu
        String query = "delete from tb_pengguna where pengguna='"+pengguna.trim()+"';";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan hapus user: " + e.getMessage());
          
        }
    }
     public static boolean ubahPassw(String pengguna, String sandi){ // true jika ada / ketemu
        String query = "update tb_pengguna set sandi ='"+sandi.trim()+"' where pengguna= '" + pengguna.trim() + "';";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            String hasil = "0";
            isi.next(); hasil = isi.getString("Jumlah");
            stat.close();
            return Integer.valueOf(hasil) > 0;
        } catch(Exception e) {
            System.out.println ("kesalahn unah passwd: " + e.getMessage());
            return false;
        }
    }
     public static void lihatUser() { // true jika ada / ketemu
        String query = "select * from tb_pengguna";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                System.out.println("Pengguna    Sandi");
                System.out.println(isi.getString("pengguna")+"  "+isi.getString("sandi"));
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat user: " + e.getMessage());
          
        }
    }
      public static void lihatPerusahaan(JTextField nama, JTextArea alamat, JTextField kota, JTextField tlp, JTextField email) { // true jika ada / ketemu
        String query = "select nama,alamat,kota,tlp,email from tb_perusahaan where id='1'";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                nama.setText(isi.getString("nama"));
                alamat.setText(isi.getString("alamat"));
                kota.setText(isi.getString("kota"));
                tlp.setText(isi.getString("tlp"));
                email.setText(isi.getString("email"));
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat user: " + e.getMessage());
          
        }
    }
     public static void tambahKonsumen(String kode, String nama, String alamat, String kota, String no_rekening, String bank, String level){ // true jika ada / ketemu
          
         String query = "insert into tb_konsumen(kode_konsumen,nama,alamat,kota,no_rekening,bank,level)" +
                "values("+kode +",'"+nama.trim()+"','"+alamat.trim()+"',"+ambilKodeKota(kota)+"," +
                "'"+no_rekening.trim()+"',"+ambilKodeBank(bank)+","+level.trim()+");";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah konsumen: " + e.getMessage());
          
        }
    }
      public static void tambahMlm(String kode_downline, String notlp){ // true jika ada / ketemu
          String kode_ref = ambilKonsumen(notlp);
         String query = "insert into tb_mlm(kode_konsumen,ref_up)" +
                "values("+kode_downline +","+kode_ref.trim()+");";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah mlm: " + e.getMessage());
          
        }
    }
      public static void tambahPerusahaan(String nama, String alamat, String kota, String tlp, String email){ // true jika ada / ketemu
       //   String kode_ref = ambilKonsumen(notlp);
          int jml = hitung_data("tb_perusahaan");
          String query="";
          if(jml>0){
              query = "update  tb_perusahaan set nama = '"+nama+"',alamat ='"+alamat+"', kota= '"+kota+"', tlp='"+tlp+"'," +
                      "email = '"+ email+"' where id = '1'";
          }else{
          query = "insert into tb_perusahaan (id,nama, alamat, kota, tlp, email) values ('1','"+nama+"','"+alamat+"','"+kota+"'," +
                 "'"+tlp+"','"+email+"');" ;
          } 

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah mlm: " + e.getMessage());
          
        }
    }
     public static void ubahKonsumen(String kode,String nama, String alamat, String kota, String no_rekening, String bank, String level){ // true jika ada / ketemu
          String query= "update tb_konsumen set nama = '"+nama.trim()+"',"+"alamat = '"+alamat.trim()+"',kota ="+ambilKodeKota(kota)+"," +
                  "no_rekening ='"+no_rekening.trim()+"' ,bank= "+ambilKodeBank(bank)+",level= "+level.trim()+" where kode_konsumen="+kode;
         

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan(ubah konsumen): " + e.getMessage());
          
        }
    }
     public static void hapusKonsumen(String kode){ // true jika ada / ketemu
          
         String query ="delete from tb_detail_konsumen where kode_konsumen = "+kode.trim() +";"+ "delete from tb_konsumen where kode_konsumen ="+kode.trim()+"; " ;
                                                                                     
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan (hapus konsumen): " + e.getMessage());
          
        }
    }
     public static void hapusDeposit(String kode){ // true jika ada / ketemu
          
         String query ="delete from tb_deposit where kode = "+kode.trim();// +";"+ "delete from tb_konsumen where kode_konsumen ="+kode.trim()+"; " ;
                                                                                     
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan (hapus deposit): " + e.getMessage());
          
        }
    }
     public static String ambilKodeKota(String namaKota){
         String hasil=null;
         String query = "select kode from tb_kota where nama like '"+ namaKota.trim()+"';";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while(isi.next()){
                hasil = isi.getString("kode");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan kode kota: " + e.getMessage());
          
        }
            
         return hasil;
     }
     public static String ambilKodeBank(String namaBank){
         String hasil=null;
         String query = "select kode from tb_bank where nama like '"+ namaBank.trim()+"';";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while(isi.next()){
                hasil = isi.getString("kode");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil kode bank: " + e.getMessage());
          
        }
            
         return hasil;
     }
      public static void tambahNoHp(String kode_konsumen, String noHp){ // true jika ada / ketemu
        String query = "insert into tb_detail_konsumen values ("+kode_konsumen.trim()+",'" +noHp.trim()+"');";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan(tambahNoHp): " + e.getMessage());
          
        }
    }
      public static void UbahNoHp(String kode_konsumen,String nohpLama, String noHpbaru){ // true jika ada / ketemu
        String query = "update tb_detail_konsumen set no_hp= '"+noHpbaru.trim()+"' " +
                "where kode_konsumen = "+kode_konsumen.trim()+" AND no_hp ='"+nohpLama.trim()+"'";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ubah no hp: " + e.getMessage());
          
        }
    }
      public static void gantiPin(String kode_konsumen,String pin_baru){ // true jika ada / ketemu
        String query = "update tb_konsumen set sandi = "+pin_baru+" where kode_konsumen = "+kode_konsumen+" ;";
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ubah pin: " + e.getMessage());
          
        }
    }
      public static void HapusNoHp(String kode_konsumen,String noHp){ // true jika ada / ketemu
        String query = "delete from tb_detail_konsumen where kode_konsumen ="+kode_konsumen.trim()+" AND no_hp ='"+noHp.trim()+"';";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan(Hapus no_hp konDB): " + e.getMessage());
          
        }
    }
       public static String lihatSaldoKonsumen(String Kode_konsumen) { // true jika ada / ketemu
        String query = "select saldo from tb_pengguna where kode_konsumen ="+Kode_konsumen;
        String Hasil=null;
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                Hasil= isi.getString("saldo");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat saldo konsumen: " + e.getMessage());
          
        }
        return Hasil;
    }
     public static void lihatKonsumen() { // true jika ada / ketemu
        String query = "select kode_konsumen as kode, nama, saldo,level from tb_pengguna";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                System.out.println("ini lg liat konsumen");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat konsumen: " + e.getMessage());
          
        }
    }
     public static void ambilDataKonsumen(String kode, JTextField nama, JTextPane alamat,JTextField norekening,
             JComboBox kota, JComboBox bank, JComboBox level) { // true jika ada / ketemu
        String query = "select  nama, alamat,no_rekening,bank,kota,level from tb_konsumen where kode_konsumen = "+kode.trim()+";";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                nama.setText(isi.getString("nama"));
                alamat.setText(isi.getString("alamat"));
                norekening.setText(isi.getString("no_rekening"));
                kota.setSelectedItem(isi.getString("kota"));
                bank.setSelectedItem(isi.getString("bank"));
                level.setSelectedItem(isi.getString("level"));
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil data konsumen: " + e.getMessage());
          
        }
    }
     public static void tambahPulsa(String kode_pulsa, String sandi){ // true jika ada / ketemu
        String query = "insert into tb_pulsa values ('" + kode_pulsa.trim() + "','"+sandi.trim()+"',0)";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tambahpulsa: " + e.getMessage());
          
        }
    }
     public static void tambahAlat(String nama, String port, String bound, String data, String parity, String stopBit,
             String flowCtrl, String no_smsc, String pulsa, String masa_aktif){ // true jika ada / ketemu
         int jml=hitung_data(" tb_alat");
         ++jml;
         System.out.println("tambah alat dijalankan");
         String query = "insert into tb_alat (kode_alat, nama ,port_com, bound, data_bit, pariti, stop_bit, flow_cntr," +
                "no_smsc, pulsa, masa_aktif ) values ("+ String.valueOf(jml) +",'" + nama.trim() + "','"+port.trim()+"',"+bound.trim()+","+data.trim()+"," +
                 "'"+parity.trim()+"',"+stopBit.trim()+",'"+flowCtrl.trim()+"',"+no_smsc.trim()+","+pulsa.trim()+","+masa_aktif.trim()+")";

        try {
            stat = con.createStatement(); 
            isi = stat.executeQuery(query);
            stat.close();
            System.out.println("tambah ok");
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah alat: " + e.getMessage());
          
        }
    }
     public static void hapusAlat(String namaAlat){
        String query = "delete from tb_alat where nama='"+namaAlat.trim()+"'";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan hapus alat: " + e.getMessage());
          
        } 
     }
     public static void ubahAlat(String nama, String port, String bound, String data, String parity, String stopBit,
             String flowCtrl, String no_smsc, String pulsa, String masa_aktif){ // true jika ada / ketemu
                
                String query = "update tb_alat set port_com='"+port.trim()+"', bound="+bound.trim()+", data_bit="+data.trim()+", pariti='"+parity.trim()+"', stop_bit="+stopBit.trim()+", flow_cntr='"+flowCtrl.trim()+"'," +
                "no_smsc="+no_smsc.trim()+", pulsa="+pulsa.trim()+", masa_aktif="+masa_aktif.trim()+"  " +
                        "where nama='"+ nama +"'";
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ubah alat: " + e.getMessage());
          
        }
    }
     public static void tambahSaldo(String kode, String saldo){ // true jika ada / ketemu
        
         System.out.println("tambah alat Saldo dijalankan");
         String query = "insert into tb_saldo (kode_konsumen,saldo) values ("+kode+" ,"+saldo+");" +
                 "update tb_konsumen set saldo = saldo + "+saldo+" where kode_konsumen = "+kode+";";
        try {
            stat = con.createStatement(); 
            isi = stat.executeQuery(query);
            stat.close();
            System.out.println("tambah ok");
        } catch(Exception e) {
            System.out.println ("Kesalahan tambah saldo: " + e.getMessage());
          
        }
    }
     
     public static String lihatSaldo(String kode){
         String saldo = "";
            String query = "select saldo from tb_konsumen where kode_konsumen = "+kode.trim()+";";
        try {
            stat = con.createStatement(); 
            isi = stat.executeQuery(query);
            while(isi.next()){
                saldo = isi.getString("saldo");
            }
            stat.close();
            System.out.println("tambah ok");
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat saldo: " + e.getMessage());
          
        }
                 
         return saldo;
     }
     public static int ambilJumlah(String tabel){
         int jml =0;
         String query = "select count(*)as jml from "+ tabel;
         try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                jml=Integer.parseInt(isi.getString("jml"));
                System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil jumlah: " + e.getMessage());
          
        }
         return jml;
         
     }
      public static int ambilJumlahTranPHari(String kode_konsumen){
         int jml =0;
         String query = "select count(*)as jml from tb_transaksi where kode_konsumen = "+ kode_konsumen+" and tgl = now()::date;";
         try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                jml=Integer.parseInt(isi.getString("jml"));
               // System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil jumlah transaksi perhari: " + e.getMessage());
          
        }
         return jml;
         
     }
      public static String ambilNoHp(String kode){
         String hasil ="";
         String query = "select * jml from tb_detail_konsumen where kode_konsumen = "+kode.trim()+ " limit 1;";
         try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                hasil=isi.getString("no_hp");
                //System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil jumlah hp: " + e.getMessage());
          
        }
         return hasil;
         
     }
      
     public static int ambilHarga(String kode_pulsa){
         System.out.println("Proses ambil harga jalan");
       int harga=0;
       String query = "select harga from tb_pulsa where kode_pulsa = '"+kode_pulsa+"';";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                harga=isi.getInt("harga");
                System.out.println("ambil harga --> "+harga);
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil harga: " + e.getMessage());
          
        }
     return harga;
     }
      public static int ambilLevel(String kode_konsumen){
         System.out.println("Proses ambil harga jalan");
       int harga=0;
       String query = "select level from tb_konsumen where kode_konsumen = "+kode_konsumen+";";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                harga=isi.getInt("level");
                System.out.println("ambil harga --> "+harga);
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil level: " + e.getMessage());
          
        }
     return harga;
     }
     
      public static String ambilKonsumen(String no_hp){
       String kode=null, query = "select kode_konsumen from tb_detail_konsumen where no_hp like '%"+no_hp+"';";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                kode=isi.getString("kode_konsumen");
                //System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil kode konsumen: " + e.getMessage());
          
        }
     return kode;
     }
      public static String ambilUpline(String kode_konsumen){
       String kode=null, query = "select ref_up from tb_mlm where kode_konsumen = "+kode_konsumen+";";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                kode=isi.getString("ref_up");
                //System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil kode upline: " + e.getMessage());
          
        }
     return kode;
     }
      public static String ambilnamaKonsumen(String no_hp){
          
       String kode=null, query = "select nama from tb_detail_konsumen where kode_konsumen = "+ambilKonsumen(no_hp)+";";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                kode=isi.getString("kode_konsumen");
                //System.out.println("JUMLAH DATA PADA TABEL: "+tabel+" = "+isi.getString("jml"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil nama konsumen: " + e.getMessage());
          
        }
     return kode;
     }
    public static void prosesGenTransIsi( String id, String notlp_7an, String kode_pulsa, String notlp) {
      /*  int jml= hitung_data("tb_transaksi");
        String harga = ambilHarga(kode_pulsa), kode_konsumen=ambilKonsumen(notlp);
         //String ambil4 = notlp_7an.substring(notlp_7an.length()-4,notlp_7an.length()); //sementara saja
        prosesIsiTransIsi(id,notlp_7an,kode_pulsa,notlp);
         String query =  "insert into tb_saldo (kode_konsumen,saldo) values("+kode_konsumen+", "+harga+");"+//coba hilangin negatif
                 "update tb_konsumen set saldo = saldo - "+harga + "" +
                 " where kode_konsumen = "+ kode_konsumen + ";";
                 */
        System.out.println("Proses gen transaksi jalan");
        String kode_konsumen=ambilKonsumen(notlp);
        int level = ambilLevel(kode_konsumen);
        int  saldo = ambilHarga(kode_pulsa);
        if (level == 1){
            saldo = saldo + 500 + 200;
        }else{
            saldo = saldo + 500 + 200+ 100;
        }
                
        System.out.println("sald0 = "+saldo);
        try {
          /*  stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
           */
            prosesIsiTransIsi(id,notlp_7an,kode_pulsa,notlp);
            prosesIsisaldo(-saldo,notlp);
            prosesUpdateSaldo(notlp,-saldo);
            
        } catch(Exception e) {
            System.out.println ("Kesalahan proses gentransaksi: " + e.getMessage());
          
        }
    }
    public static void prosesIsiTransIsi( String idsms, String notlp_7an, String kode_pulsa, String notlp) {
        System.out.println("Proses isi transaksi jalan");
        int jml= hitung_data("tb_transaksi");
       // String harga = ambilHarga(kode_pulsa), 
        String query="";
        if (idsms == null && notlp==null){
             query = "insert into tb_transaksi(no_tujuan,  kode_pulsa)" +
                 "values("+notlp_7an+",'"+kode_pulsa+"');" ;
        }else{
            String kode_konsumen=ambilKonsumen(notlp);
         //String ambil4 = notlp_7an.substring(notlp_7an.length()-4,notlp_7an.length()); //sementara saja
        query = "insert into tb_transaksi(no_tujuan, kode_sms, kode_pulsa, kode_konsumen)" +
                 "values("+notlp_7an+", "+idsms+",'"+kode_pulsa+"', " + kode_konsumen+ ");" ;
        }
        

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan proses Isi Transaksi: " + e.getMessage());
          
        }
    }
     public static void prosesIsisaldo(int saldo, String notlp) {
         System.out.println("proses isi saldo jalan");
       // int jml= hitung_data("tb_transaksi");
        //String harga = ambilHarga(kode_pulsa);//, 
        String kode_konsumen=ambilKonsumen(notlp);
        String kode_upline = ambilUpline(kode_konsumen);
        String query="";
        if (kode_upline==null){
         //String ambil4 = notlp_7an.substring(notlp_7an.length()-4,notlp_7an.length()); //sementara saja
     //   System.out.println("saldo ="+saldo);
          query ="insert into tb_saldo (kode_konsumen,saldo) values("+kode_konsumen+", "+saldo+");";//coba hilangin negatif
                 
        }else{
            query ="insert into tb_saldo (kode_konsumen,saldo) values("+kode_konsumen+", "+saldo+");" +
                    "insert into tb_saldo (kode_konsumen,saldo) values("+kode_upline+", "+100+");";//coba hilangin negatif
        }
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan proses Isi saldo: " + e.getMessage());
          
        }
    }
     public static void prosesUpdateSaldo(String notlp, int saldo) {
         System.out.println("proases ambil saldo jalan");
      //  int jml= hitung_data("tb_transaksi");
        //String harga = ambilHarga(kode_pulsa);//, 
        String kode_konsumen=ambilKonsumen(notlp);
         //String ambil4 = notlp_7an.substring(notlp_7an.length()-4,notlp_7an.length()); //sementara saja
        String kode_upline = ambilUpline(kode_konsumen);
        String query="";
        if (kode_upline==null){
             query = "update tb_konsumen set saldo = saldo + "+ saldo + "" +
                 " where kode_konsumen = "+ kode_konsumen + ";";
        }else{
            query = "update tb_konsumen set saldo = saldo + "+ saldo + "" +
                 " where kode_konsumen = "+ kode_konsumen + ";" +
                 "update tb_konsumen set saldo = saldo + "+ 100 +" " +
                 " where kode_konsumen = "+ kode_upline + ";" ;
        }
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan proses Update saldo: " + e.getMessage());
          
        }
    }
    public static String ambilKodePulsa(String kode_pulsa){
       String kode=null, query = "select a.operator_e, a.nominal,b.kode_pulsa as kode from tb_pulsa as a , tb_distributor_det as b " +
               "WHERE a.kode_pulsa = '"+ kode_pulsa +"' and a.operator_e = b.operator_e and a.nominal=b.nominal " +
               "and a.kode_distributor=b.kode_distributor";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                kode=isi.getString("kode");
                System.out.println("Kode pulsanya : "+isi.getString("kode"));
               
           
                        
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil kode pulsa: " + e.getMessage());
          
        }
     return kode;
     }
    public static String[] ambilSandi_gatway(String kode_pulsa){
        System.out.println("Ambil sandi n gateway");
        String data[] = new String[2];
       String kode=null, query = "select a.kode_distributor,b.sandi,b.no_gateway from tb_pulsa as a, tb_distributor as b " +
               "where a.kode_pulsa = '"+kode_pulsa+"' and a.kode_distributor = b.kode_distributor";
       try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                data[0]=isi.getString("sandi");
                data[1]=isi.getString("no_gateway");
                System.out.println("sandi : "+isi.getString("sandi") +"; no gateway = "+isi.getString("no_gateway"));
                
            }
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambli sandi: " + e.getMessage());
          
        }
     return data;
     }
     public static void tuliskeTabelKirim(String notlp, String dataKirim){ // true jika ada / ketemu
        String query =  "Insert Into tb_sms_kirim (no_tujuan,isi_sms,sts) Values ('"+notlp+"','"+dataKirim+"',0);";

        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan tuliskeTabelKirim: " + e.getMessage());
          
        }
    }
     public static void tambahdeposit(String notlp, String nominal, String no_ref, String nama){ // true jika ada / ketemu
        
         System.out.println("tambah deposit dijalankan");
         String kode = ambilKonsumen(notlp);
         String query = "insert into tb_deposit (kode_konsumen, nama, nominal, no_ref,notlp) values(" +
                 ""+kode+",'"+nama+"',"+nominal+",'"+no_ref+"','"+notlp+"');";
        try {
            stat = con.createStatement(); 
            isi = stat.executeQuery(query);
            stat.close();
            System.out.println("tambah ok");
        } catch(Exception e) {
           System.out.println ("Kesalahan tambah deposit: " + e.getMessage());
          
        }
    }
     public static String ambilHpDeposit(String kode){
         String hasil=null;
         String query = "select notlp from tb_deposit where kode = "+ kode.trim()+";";
        
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while(isi.next()){
                hasil = isi.getString("notlp");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan ambil no hp deposit : " + e.getMessage());
          
        }
            
         return hasil;
     }
     public static void ubahDeposit(String no){ // true jika ada / ketemu
        
         //System.out.println("tambah alat Saldo dijalankan");
         String query = "update tb_deposit set status = '1'  where kode = "+no+";";
        try {
            stat = con.createStatement(); 
            isi = stat.executeQuery(query);
            stat.close();
            System.out.println("ubah  ok");
        } catch(Exception e) {
            System.out.println ("Kesalahan ubah deposit: " + e.getMessage());
          
        }
    }
     public static String ambilMaxKonsumen() { // true jika ada / ketemu
        String query = "select max(kode_konsumen)as kode from tb_konsumen ;";
        String kode=null;
        try {
            stat = con.createStatement(); isi = stat.executeQuery(query);
            while (isi.next())
            {
                kode= isi.getString("kode");
            }
            stat.close();
            
        } catch(Exception e) {
            System.out.println ("Kesalahan lihat saldo konsumen: " + e.getMessage());
          
        }
        return kode;
    }
      
}
